﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;    

namespace MISAPP.Classify.DataAccess
{
    public class DA_MISParameter:DA_Matrix
    {

        public DA_MISParameter()
        {
            tableName = "MISParameter";
        }

        /// <summary>
        /// 回傳Distinct fldname 後的table
        /// </summary>
        /// <param name="p_team">MISParameter.team</param>
        /// <returns></returns>
        public System.Collections.ArrayList GetDistinctFldname(string p_team)
        {
            FillTableSchema("MISParameter");

            string sql = @"SELECT DISTINCT A.fldname 
                                FROM MISParameter A
                                WHERE 1=1 
                                  AND (team = @team1) 
                                ";

            FillFieldList(new string[] { p_team }, new string[] { "@team1" }, "team");

            AddParameter(sql);
            DataTable para = getData();
            System.Collections.ArrayList al = new System.Collections.ArrayList();
            foreach (DataRow row in para.Rows)
            {
                al.Add(row[0].ToString());
            }
            return al;
        }

        public DataTable GetParameter(string p_team,string p_para1)
        {
            return GetParameter(p_team, p_para1, string.Empty);
        }

        public DataTable GetParameter(string p_team, string p_para1,string p_ASCDSC)
        {
            FillTableSchema("MISParameter");

            string sql = string.Format( @"SELECT fldname
                                    , CASE 
	                                    WHEN (fldvalue IS NULL) OR (fldvalue = '') THEN 
		                                    fldname 
	                                    ELSE fldvalue 
	                                    END AS fldvalue
                                    , team
                                FROM MISParameter
                                WHERE 1=1 
                                  AND (para1 = @para1) 
                                  AND (team = @team1) 
                                ORDER BY SEQ {0}",p_ASCDSC);

            FillFieldList(new string[] { p_para1 }, new string[] { "@para1" }, "para1");
            FillFieldList(new string[] { p_team }, new string[] { "@team1" }, "team");

            AddParameter(sql);
            return getData();
        }

        /// <summary>
        /// 取得parameter 資料，由外部傳入SQL
        /// </summary>
        /// <param name="p_fldNames"></param>
        /// <param name="p_fldValues"></param>
        /// <param name="p_paras"></param>
        /// <param name="p_sql"></param>
        /// <returns></returns>
        public DataTable GetParameterBySQL(string[] p_fldNames, string[] p_fldValues
            , string[] p_paras, string p_sql)
        {
            FillTableSchema(tableName);
            FillFieldList(p_fldValues, p_paras, p_fldNames);
            AddParameter(p_sql);
            return getData();
        }

        protected string GetGroupByStatement()
        {
            return string.Empty;
        }

        public bool Update(string p_team, string p_para1, string p_fldName
            , string p_fldValue)
        {
            string sql = @"
UPDATE MISParameter SET fldvalue=@p_fldValue
WHERE team = @p_team
AND para1 = @p_para1
AND fldname LIKE p_fldname
";
            FillTableSchema(tableName);
            whereList = new List<string>();
            fieldList = new List<field>();

            FillFieldList(new string[] { p_team }, new string[] { "@p_team" }, "team");
            FillFieldList(new string[] { p_para1 }, new string[] { "@p_para1" }, "para1");
            FillFieldList(new string[] { p_fldName }, new string[] { "@p_sheetName" }, "fldname");
            FillFieldList(new string[] { p_fldValue }, new string[] { "@p_fldValue" }, "p_fldValue");

            AddParameter(sql);
            int iResult = Common.DAL.ExecuteNoneQuery(cmd);
            return iResult < 0 ? false : true;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="p_fldNames">欄位名陣列</param>
        /// <param name="p_fldValues">欄位值陣列</param>
        /// <param name="p_paras">parameter陣列</param>
        /// <param name="p_sql">指令</param>
        /// <returns></returns>
        public bool ExecuteByOutSiteStatement(string[] p_fldNames, string[] p_fldValues
            ,string[] p_paras,string p_sql)
        {
            FillTableSchema(tableName);
            FillFieldList(p_fldValues, p_paras, p_fldNames);
            AddParameter(p_sql);
            int iResult = Common.DAL.ExecuteNoneQuery(cmd);
            return iResult < 0 ? false : true;
        }

    }
}
